/**
 * DaoUser.java
 *2013年9月23日下午7:46:52
 *LINKT
 */
package com.jskj.dao;



import java.sql.ResultSet;
import java.sql.SQLException;

import com.jskj.bean.User;
import com.jskj.commons.DBConn;

/**
 * @author LINKT
 *
 */
public class DaoUser {

        /**
         * 根据用户名和密码获取用户信息
         * @param loginname
         * @param loginpwd
         * @return
         */
        public User getUserForLogin(String loginname,String loginpwd){
                User user=new User();
                DBConn conn=new DBConn();//select * from teczry where flogname='admin' and fpassword='888'
                ResultSet rs=conn.SelectSQL("select * from teczry where flogname='"+loginname+"' and fpassword='"+loginpwd+"'");
                try {
                        if (rs.next()) {
                                user.setfID(rs.getInt("fid"));
                                user.setfLogName(rs.getString("fLogName"));
                                user.setfPassword(rs.getString("fPassword"));
                                user.setfPower(rs.getString("fPower"));;
                                user.setfUserName(rs.getString("fUserName"));
                                user.setSWJG_DM(rs.getString("sWJG_DM"));
                                user.setYXWS(rs.getInt("yXWS"));
                        }
                        rs.close();
                        conn.Close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return user;
        }
        
        public String getUserList(int page ,int limit,String sql){
                DBConn conn=new DBConn();
                String str="";
                int rowEnd=page*limit;
                String sSQL="select * from ("+
                                "    select top "+limit+" * from("+
                                "   select top "+rowEnd+" * "+
                                "    from ("+sql+") as aa order by fid) as bb order by fid desc ) as cc order by fid ";
                int totalcount=conn.SelectSQLCount(sql);
                StringBuffer sb=new StringBuffer();
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"flogname\":\""+rs.getString("flogname")+"\",\"fusername\":\""+rs.getString("fusername")+"\",\"fpassword\":\""+rs.getString("fpassword")+"\","
                                                + "\"fpower\":\""+rs.getString("fpower")+"\","
                                                + "\"swjg_dm\":\""+rs.getString("swjg_dm")+"\","
                                                + "\"swjg_mc\":\""+rs.getString("swjg_mc")+"\""
                                                                + "},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
        public boolean addUser(User user){
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from teczry where flogname='"+user.getfLogName()+"'")==0) {
                        conn.ExecuteSQL("INSERT INTO [taxoil].[dbo].[tECzry]([fLogName], [fUserName], [fPassword], [fPower], [SWJG_DM])"+
                                                        "VALUES('"+user.getfLogName()+"', '"+user.getfUserName()+"', '"+user.getfPassword()+"', '"+user.getfPower()+"', '"+user.getSWJG_DM()+"')");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
        public boolean modifUser(User user){
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from teczry where flogname='"+user.getfLogName()+"'")>0) {
                        conn.ExecuteSQL("UPDATE [taxoil].[dbo].[tECzry] "+
                                                        "SET [fUserName]='"+user.getfUserName()+"', [fPassword]='"+user.getfPassword()+"', [fPower]='"+user.getfPower()+"', [SWJG_DM]='"+user.getSWJG_DM()+"' "+
                                                                " WHERE [fLogName]='"+user.getfLogName()+"'");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
        public boolean delUser(User user){
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from teczry where flogname='"+user.getfLogName()+"'")>0) {
                        conn.ExecuteSQL("DELETE FROM [taxoil].[dbo].[tECzry] "+
                                                        "WHERE [fLogName]='"+user.getfLogName()+"'");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
        public boolean changeUserPwd(String flogname, String oldPwd ,String newPwd){
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from teczry where flogname='"+flogname+"' and fPassword='"+oldPwd+"'")>0) {
                        conn.ExecuteSQL("UPDATE [taxoil].[dbo].[tECzry] "+
                                                        "SET [fPassword]='"+newPwd+"' "+
                                                        " WHERE [fLogName]='"+flogname+"'");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
}
